# -*- coding: utf-8 -*-
"""
角色管理数据访问层

负责角色、权限、销售员客户关系等相关的数据库操作
"""
from typing import List, Dict, Any, Optional
from datetime import datetime, date
from .base_repository import BaseRepository


class RoleRepository(BaseRepository):
    """角色权限数据访问类"""
    
    def __init__(self):
        super().__init__("users")
    
    def get_user_permissions(self, user_role: str) -> List[Dict[str, Any]]:
        """获取用户角色的所有权限"""
        sql = """
            SELECT p.* FROM permissions p
            INNER JOIN role_permissions rp ON p.permission_id = rp.permission_id
            WHERE rp.role_code = %s AND p.status = 1
            ORDER BY p.resource_type, p.sort_order
        """
        return self.execute_custom_query(sql, [user_role], True) or []
    
    def check_permission(self, user_role: str, permission_code: str) -> bool:
        """检查用户是否有指定权限"""
        sql = """
            SELECT COUNT(*) as count FROM permissions p
            INNER JOIN role_permissions rp ON p.permission_id = rp.permission_id
            WHERE rp.role_code = %s AND p.permission_code = %s AND p.status = 1
        """
        result = self.execute_custom_query(sql, [user_role, permission_code], True)
        return result[0]['count'] > 0 if result else False
    
    def get_page_permissions(self, user_role: str) -> List[str]:
        """获取用户可访问的页面路径"""
        sql = """
            SELECT p.resource_path FROM permissions p
            INNER JOIN role_permissions rp ON p.permission_id = rp.permission_id
            WHERE rp.role_code = %s AND p.resource_type = 'page' AND p.status = 1
        """
        result = self.execute_custom_query(sql, [user_role], True)
        return [row['resource_path'] for row in result] if result else []
    
    def update_user_role(self, user_id: str, new_role: str, updated_by: Optional[str] = None) -> bool:
        """更新用户角色"""
        update_sql = """
            UPDATE users SET 
                user_role = %s,
                updated_time = NOW()
            WHERE user_id = %s
        """
        result = self.execute_custom_query(update_sql, [new_role, user_id], False)
        return result is not None


class SalesmanRepository(BaseRepository):
    """销售员数据访问类"""
    
    def __init__(self):
        super().__init__("users")
    
    def get_salesman_by_id(self, salesman_id: str) -> Optional[Dict[str, Any]]:
        """根据ID获取销售员信息"""
        sql = """
            SELECT * FROM users 
            WHERE user_id = %s AND user_role = 'salesman' AND is_deleted = 0 AND status = 1
        """
        result = self.execute_custom_query(sql, [salesman_id], True)
        return result[0] if result else None
    
    def get_salesman_customers(self, salesman_id: str, status: int = 1) -> List[Dict[str, Any]]:
        """获取销售员的客户列表"""
        sql = """
            SELECT 
                sc.*,
                u.user_id, u.user_no, u.nickname, u.phone, u.email,
                u.wechat_nickname, u.wechat_avatar, u.created_time as customer_created_time
            FROM salesman_customers sc
            INNER JOIN users u ON sc.customer_id = u.user_id
            WHERE sc.salesman_id = %s AND sc.status = %s AND u.is_deleted = 0
            ORDER BY sc.created_time DESC
        """
        return self.execute_custom_query(sql, [salesman_id, status], True) or []
    
    def add_customer_to_salesman(self, salesman_id: str, customer_id: str, 
                                relationship_type: str = 'assigned', created_by: Optional[str] = None) -> Optional[str]:
        """为销售员分配客户"""
        import uuid
        relation_id = str(uuid.uuid4())
        
        insert_sql = """
            INSERT INTO salesman_customers (
                relation_id, salesman_id, customer_id, relationship_type,
                start_date, status, created_time, created_by
            ) VALUES (%s, %s, %s, %s, CURDATE(), 1, NOW(), %s)
        """
        result = self.execute_custom_query(insert_sql, [
            relation_id, salesman_id, customer_id, relationship_type, created_by
        ], False)
        return relation_id if result is not None else None
    
    def remove_customer_from_salesman(self, salesman_id: str, customer_id: str) -> bool:
        """移除销售员的客户关系"""
        update_sql = """
            UPDATE salesman_customers SET 
                status = 0, 
                end_date = CURDATE(),
                updated_time = NOW()
            WHERE salesman_id = %s AND customer_id = %s
        """
        result = self.execute_custom_query(update_sql, [salesman_id, customer_id], False)
        return result is not None
    
    def get_customer_salesman(self, customer_id: str) -> Optional[Dict[str, Any]]:
        """获取客户的服务销售员"""
        sql = """
            SELECT 
                sc.*,
                u.user_id, u.user_no, u.nickname, u.phone, u.salesman_no,
                u.department, u.salesman_level
            FROM salesman_customers sc
            INNER JOIN users u ON sc.salesman_id = u.user_id
            WHERE sc.customer_id = %s AND sc.status = 1 AND u.is_deleted = 0
            ORDER BY sc.created_time DESC
            LIMIT 1
        """
        result = self.execute_custom_query(sql, [customer_id], True)
        return result[0] if result else None


class CustomerRepository(BaseRepository):
    """客户数据访问类"""
    
    def __init__(self):
        super().__init__("users")
    
    def get_customers_list(self, salesman_id: Optional[str] = None, page: int = 1, page_size: int = 20) -> Dict[str, Any]:
        """获取客户列表（支持销售员筛选和分页）"""
        where_clause = "WHERE u.user_role = 'customer' AND u.is_deleted = 0 AND u.status = 1"
        params = []
        
        if salesman_id:
            where_clause += " AND EXISTS (SELECT 1 FROM salesman_customers sc WHERE sc.customer_id = u.user_id AND sc.salesman_id = %s AND sc.status = 1)"
            params.append(salesman_id)
        
        # 查询总数
        count_sql = f"SELECT COUNT(*) as total FROM users u {where_clause}"
        count_result = self.execute_custom_query(count_sql, params, True)
        total = count_result[0]['total'] if count_result else 0
        
        # 查询数据
        offset = (page - 1) * page_size
        data_sql = f"""
            SELECT 
                u.*,
                sc.salesman_id,
                s.nickname as salesman_name,
                s.salesman_no
            FROM users u
            LEFT JOIN salesman_customers sc ON u.user_id = sc.customer_id AND sc.status = 1
            LEFT JOIN users s ON sc.salesman_id = s.user_id
            {where_clause}
            ORDER BY u.created_time DESC
            LIMIT %s OFFSET %s
        """
        params.extend([page_size, offset])
        customers = self.execute_custom_query(data_sql, params, True) or []
        
        return {
            "customers": customers,
            "total": total,
            "page": page,
            "page_size": page_size,
            "total_pages": (total + page_size - 1) // page_size
        }
    
    def create_customer(self, customer_data: Dict[str, Any]) -> Optional[str]:
        """创建新客户"""
        from datetime import datetime
        
        user_id = f"U{int(datetime.now().timestamp() * 1000)}"
        user_no = f"USER{datetime.now().strftime('%Y%m%d%H%M%S')}"
        
        insert_sql = """
            INSERT INTO users (
                user_id, user_no, nickname, phone, email, avatar,
                user_role, status, created_time
            ) VALUES (
                %s, %s, %s, %s, %s, %s, 'customer', 1, NOW()
            )
        """
        
        params = [
            user_id, user_no,
            customer_data.get('nickname'),
            customer_data.get('phone'),
            customer_data.get('email'),
            customer_data.get('avatar', '')
        ]
        
        result = self.execute_custom_query(insert_sql, params, False)
        return user_id if result is not None else None
    
    def search_customers(self, keyword: str, salesman_id: Optional[str] = None) -> List[Dict[str, Any]]:
        """搜索客户"""
        where_clause = """
            WHERE u.user_role = 'customer' AND u.is_deleted = 0 AND u.status = 1
            AND (u.nickname LIKE %s OR u.phone LIKE %s OR u.email LIKE %s OR u.user_no LIKE %s)
        """
        search_term = f"%{keyword}%"
        params = [search_term, search_term, search_term, search_term]
        
        if salesman_id:
            where_clause += " AND EXISTS (SELECT 1 FROM salesman_customers sc WHERE sc.customer_id = u.user_id AND sc.salesman_id = %s AND sc.status = 1)"
            params.append(salesman_id)
        
        sql = f"""
            SELECT 
                u.*,
                sc.salesman_id,
                s.nickname as salesman_name,
                s.salesman_no
            FROM users u
            LEFT JOIN salesman_customers sc ON u.user_id = sc.customer_id AND sc.status = 1
            LEFT JOIN users s ON sc.salesman_id = s.user_id
            {where_clause}
            ORDER BY u.created_time DESC
            LIMIT 50
        """
        
        return self.execute_custom_query(sql, params, True) or []


class OrderRepository(BaseRepository):
    """订单数据访问类"""
    
    def __init__(self):
        super().__init__()
    
    def create_proxy_order(self, order_data: Dict) -> str:
        """创建代理订单"""
        return "ORDER_001"
    
    def get_order_by_trade_no(self, trade_no: str) -> Optional[Dict]:
        """根据交易号获取订单"""
        query = "SELECT * FROM trade_orders WHERE tid = %s"
        return self.execute_query(query, [trade_no], fetchall=False)
    
    def execute_custom_query(self, query: str, params: List = None, fetchall: bool = True):
        """执行自定义查询"""
        return self.execute_query(query, params, fetchall)


class SalesStatisticsRepository(BaseRepository):
    """销售统计数据访问类"""
    
    def __init__(self):
        super().__init__("sales_statistics")
    
    def get_salesman_statistics(self, salesman_id: str, start_date: Optional[date] = None, 
                               end_date: Optional[date] = None) -> List[Dict[str, Any]]:
        """获取销售员统计数据"""
        where_clause = "WHERE salesman_id = %s"
        params = [salesman_id]
        
        if start_date:
            where_clause += " AND stat_date >= %s"
            params.append(start_date)
        
        if end_date:
            where_clause += " AND stat_date <= %s"
            params.append(end_date)
        
        sql = f"""
            SELECT * FROM sales_statistics 
            {where_clause}
            ORDER BY stat_date DESC
        """
        
        return self.execute_custom_query(sql, params, True) or []
    
    def get_salesman_today_stats(self, salesman_id: str) -> Dict[str, Any]:
        """获取销售员今日统计"""
        sql = """
            SELECT 
                COUNT(DISTINCT sc.customer_id) as customer_count,
                COUNT(o.order_id) as order_count,
                COALESCE(SUM(o.final_amount), 0) as order_amount
            FROM salesman_customers sc
            LEFT JOIN orders o ON sc.customer_id = o.customer_id 
                AND DATE(o.created_time) = CURDATE()
                AND o.payment_status = 'paid'
            WHERE sc.salesman_id = %s AND sc.status = 1
        """
        
        result = self.execute_custom_query(sql, [salesman_id], True)
        return result[0] if result else {
            'customer_count': 0,
            'order_count': 0,
            'order_amount': 0
        }
    
    def update_daily_statistics(self, salesman_id: str, stat_date: date) -> bool:
        """更新指定日期的销售统计"""
        # 先计算统计数据
        calc_sql = """
            SELECT 
                COUNT(DISTINCT sc.customer_id) as customer_count,
                COUNT(o.order_id) as order_count,
                COALESCE(SUM(o.final_amount), 0) as order_amount
            FROM salesman_customers sc
            LEFT JOIN orders o ON sc.customer_id = o.customer_id 
                AND DATE(o.created_time) = %s
                AND o.payment_status = 'paid'
            WHERE sc.salesman_id = %s AND sc.status = 1
        """
        
        result = self.execute_custom_query(calc_sql, [stat_date, salesman_id], True)
        if not result:
            return False
        
        stats = result[0]
        commission_rate = 5.00  # 5% 提成率
        commission_amount = float(stats['order_amount']) * commission_rate / 100
        
        # 插入或更新统计数据
        upsert_sql = """
            INSERT INTO sales_statistics (
                stat_id, salesman_id, stat_date, customer_count, order_count,
                order_amount, commission_rate, commission_amount, created_time
            ) VALUES (
                CONCAT('STAT', %s, %s), %s, %s, %s, %s, %s, %s, %s, NOW()
            ) ON DUPLICATE KEY UPDATE
                customer_count = VALUES(customer_count),
                order_count = VALUES(order_count),
                order_amount = VALUES(order_amount),
                commission_amount = VALUES(commission_amount),
                updated_time = NOW()
        """
        
        date_str = stat_date.strftime('%Y%m%d')
        stat_id_suffix = f"{salesman_id}_{date_str}"
        
        params = [
            salesman_id, date_str, salesman_id, stat_date,
            stats['customer_count'], stats['order_count'],
            stats['order_amount'], commission_rate, commission_amount
        ]
        
        result = self.execute_custom_query(upsert_sql, params, False)
        return result is not None 
    def execute_custom_query(self, query: str, params: tuple = None, *args, **kwargs):
        """执行自定义查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor(dictionary=True)
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchall()
            cursor.close()
            connection.close()
            
            return result if result else []
        except Exception as e:
            print(f"查询错误: {e}")
            return []
    
    def execute_query(self, query: str, params: tuple = None, *args, **kwargs):
        """执行查询"""
        return self.execute_custom_query(query, params, *args, **kwargs)
    
    def count_query(self, query: str, params: tuple = None, *args, **kwargs):
        """计数查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor()
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchone()
            cursor.close()
            connection.close()
            
            return result[0] if result else 0
        except Exception as e:
            print(f"计数错误: {e}")
            return 0
